package org.zf.database.server.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.PatternPool;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.zf.database.consts.DBPrivsType;
import org.zf.database.consts.DBType;
import org.zf.database.consts.FieldType;
import org.zf.database.domain.*;
import org.zf.database.exception.NotSupportException;
import org.zf.database.server.AbstractCommonDBServer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

@Slf4j
public class DB2DBServerImpl extends AbstractCommonDBServer
{
    @Getter
    private final Map<FieldType, String> DEFAULTMAP = new HashMap<FieldType, String>()
    {{
        put(FieldType.INT, "INTEGER");
        put(FieldType.NUMBER, "NUMERIC");
        put(FieldType.VARCHAR, "VARCHAR({})");
        put(FieldType.BOOLEAN, "BIT");
        put(FieldType.DATE, "DATE");
        put(FieldType.TIMESTAMP, "TIMESTAMP");
        put(FieldType.CLOB, "TEXT");
        put(FieldType.BLOB, "BYTE");
    }};
    private final Map<FieldType, List<String>> TYPEMAP = new HashMap<FieldType, List<String>>()
    {{
        put(FieldType.INT, Arrays.asList("INT,INTEGER,MEDIUMINT,SMALLINT,TINYINT".split(",")));
        put(FieldType.NUMBER, Arrays.asList("BIGINT,FLOAT,DOUBLE,DECIMAL".split(",")));
        put(FieldType.VARCHAR, Arrays.asList("CHAR,VARCHAR,TINYTEXT".split(",")));
        put(FieldType.BOOLEAN, Arrays.asList("BIT".split(",")));
        put(FieldType.DATE, Arrays.asList("DATE,TIME,YEAR,DATETIME".split(",")));
        put(FieldType.TIMESTAMP, Arrays.asList("TIMESTAMP".split(",")));
        put(FieldType.CLOB, Arrays.asList("TEXT,MEDIUMTEXT,LONGTEXT".split(",")));
        put(FieldType.BLOB, Arrays.asList("BLOB,MEDIUMBLOB,LONGBLOB".split(",")));
    }};

    @Override
    public List<TableSpaceInfo> getTableSpace(DBInfo dbInfo) throws Exception
    {
        String sql = "SELECT SUBSTR(TABLESPACE_NAME,1,20), bigint(TOTAL_PAGES * PAGE_SIZE), used_pages*PAGE_SIZE, free_pages*PAGE_SIZE FROM TABLE(snapshot_tbs_cfg('FJDC2', -2)) AS snapshot_tbs_cfg";
        List<TableSpaceInfo> list = new ArrayList<>();

        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(sql);
        )
        {
            while (rs.next())
            {
                TableSpaceInfo tmp = new TableSpaceInfo();
                tmp.setDbInfo(dbInfo);
                tmp.setSpaceName(rs.getString(1));
                tmp.setTotal(rs.getLong(2));
                tmp.setUsed(rs.getLong(3));
                tmp.setFree(rs.getLong(4));
                list.add(tmp);
            }
        }
        return list;
    }

    @Override
    public List<String> user(DBInfo dbInfo) throws Exception
    {
        List<String> user = super.user(dbInfo);
        if (CollUtil.isEmpty(user))
        {
            Set<String> userSet = new HashSet<>();
            try (
                    Connection conn = getConn(dbInfo);
                    Statement sm = conn.createStatement();
                    ResultSet rs = sm.executeQuery("select distinct TABSCHEMA from SYSIBM.SYSUSERAUTH");
            )
            {
                while (rs.next())
                {
                    userSet.add(rs.getString(1).trim());
                }
                return new ArrayList<>(userSet);
            } catch (Exception e)
            {
                log.error("查询异常", e);
                throw e;
            }
        }
        return user;
    }

    @Override
    public List<TableInfo> table(DBInfo dbInfo) throws Exception
    {
        List<TableInfo> table = super.table(dbInfo);
        if (CollUtil.isNotEmpty(table))
            return table;
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(String.format("SELECT VARCHAR(TABNAME,50)  AS TABNAME,TYPE,REMARKS FROM SYSCAT.TABLES WHERE TABSCHEMA = '%s'", dbInfo.getOwner()));
        )
        {
            table = new ArrayList<>();
            while (rs.next())
            {
                table.add(new TableInfo()
                {{
                    setDbInfo(dbInfo);
                    setTableName(rs.getString(1));
                    setIsTable("T".equals(rs.getString(2)));
                    setComment(rs.getString(3));
                }});
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return table;
    }

    @Override
    public TablePrivsInfo tablePrivs(TableInfo tableInfo) throws Exception
    {
        return new TablePrivsInfo(tableInfo, new HashMap<DBPrivsType, Boolean>()
        {{
            put(DBPrivsType.SELECT, true);
            put(DBPrivsType.INSERT, true);
            put(DBPrivsType.UPDATE, true);
            put(DBPrivsType.DELETE, true);
        }});
    }

    @Override
    public Map<DBPrivsType, Boolean> dbPrivs(DBInfo dbInfo) throws Exception
    {
        return new HashMap<DBPrivsType, Boolean>()
        {{
            put(DBPrivsType.DROP, true);
            put(DBPrivsType.CREATE, true);
        }};
    }

    @Override
    public TableInfo tableCount(TableInfo tableInfo) throws Exception
    {
        tableInfo.setDataSize(countTable(tableInfo));
//        try (Connection conn = getConn(tableInfo.getDbInfo());
//             Statement sm = conn.createStatement();
//             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT table_rows, index_length, data_length FROM information_schema.tables where table_schema = '{}' AND table_name = '{}'", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
//        )
//        {
//            if (rs.next())
//            {
//                tableInfo.setRowSize(rs.getInt(1));
//                tableInfo.setIndexSize(rs.getLong(2));
//                return tableInfo;
//            }
//        }
        return tableInfo;
    }

    @Override
    public List<FieldInfo> cols(TableInfo tableInfo) throws Exception
    {
        List<FieldInfo> cols = super.cols(tableInfo);
        if (CollUtil.isNotEmpty(cols))
            return cols;
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(String.format("SELECT t1.COLNAME,t1.TYPENAME,t1.LENGTH,t1.NULLS, t1.remarks,t2.CONSTNAME,t2.COLSEQ FROM SYSCAT.COLUMNS t1 left join (select a.CONSTNAME,a.COLSEQ,a.TBCREATOR,b.tabname,a.COLNAME from sysibm.syskeycoluse  a, syscat. tabconst  b  where  a.tbname=b.tabname and  b.type='P') t2 on t1.COLNAME=t2.COLNAME and t1.TABSCHEMA=t2.TBCREATOR and t1.TABNAME=t2.tabname WHERE t1.TABNAME='%s' and t1.TABSCHEMA = '%s'", tableInfo.getTableName(), tableInfo.getDbInfo().getOwner()));
        )
        {
            cols = new ArrayList<>();
            while (rs.next())
            {
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setTableInfo(tableInfo);
                fieldInfo.setFieldName(rs.getString(1));
                fieldInfo.setCommonType(convertFieldType(rs.getString(2)));
                fieldInfo.setSourceType(rs.getString(2));
                fieldInfo.setLength(Integer.parseInt(rs.getString(3)));
                fieldInfo.setNullable("N".equals(rs.getInt(4)));
                fieldInfo.setComment(rs.getString(5));
                String pkName = rs.getString(6);
                if (pkName != null)
                {
                    fieldInfo.setIsPK(true);
                    fieldInfo.setPkInd(rs.getInt(7));
                } else
                {
                    fieldInfo.setIsPK(false);
                }
                cols.add(fieldInfo);
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return cols;
    }

    @Override
    public DBInfo url2DBInfo(String jdbcUrl)
    {
        if (jdbcUrl.indexOf("?") == -1)
        {
            jdbcUrl += "?";
        }
        List<String> allGroups = ReUtil.getAllGroups(PatternPool.get("jdbc:db2://(.*?):(.*?)/(.*?)\\?(.*)", 32), jdbcUrl, false);
        ConnInfo connInfo = new ConnInfo();
        connInfo.setIp(allGroups.get(0));
        connInfo.setPort(NumberUtil.parseInt(allGroups.get(1)));
        connInfo.setDbName(allGroups.get(2));
        connInfo.setAttr(allGroups.get(3));
        DBInfo dbInfo = new DBInfo(DBType.DB2);
        dbInfo.setJdbcUrl(jdbcUrl);
        dbInfo.setConnInfo(connInfo);
        return dbInfo;
    }

    @Override
    public String dbInfo2Url(DBInfo dbInfo)
    {
        ConnInfo connInfo = dbInfo.getConnInfo();
        if (connInfo == null)
            throw new NoSuchElementException("未找到连接配置信息");
        return StrUtil.isEmpty(connInfo.getAttr()) ? String.format("jdbc:db2://%s:%s/%s", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName()) : String.format("jdbc:db2://%s:%s/%s?%s", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName(), connInfo.getAttr());
    }

    @Override
    public FieldType convertFieldType(String type) throws NotSupportException
    {

        return super.convertFieldType(TYPEMAP, type);
    }

    @Override
    public String escapeCharacter(String sql)
    {
        return sql.replace("\\", "\\\\").replace("'", "\\'");
    }

    @Override
    public String updateColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        String dbName = fieldInfo.getTableInfo().getDbInfo().getOwner();
        String tableName = fieldInfo.getTableInfo().getTableName();
        String colName = fieldInfo.getFieldName();
        String defaults = fieldInfo.getDefaultStr();
        if (!getDEFAULTMAP().containsKey(fieldInfo.getCommonType()))
            throw new NotSupportException("不支持的字段类型:" + fieldInfo.getCommonType().getType());
        return StrUtil.format("ALTER TABLE {}.{} MODIFY COLUMN {} {} {} {}", dbName, tableName, colName, getTypeSql(fieldInfo), StrUtil.isBlank(defaults) ? " " : " DEFAULT " + defaults, StrUtil.isEmpty(fieldInfo.getComment()) ? " " : StrUtil.format("COMMENT '{}'", fieldInfo.getComment()));
    }

    @Override
    public String delColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        return StrUtil.format("ALTER TABLE {}.{} DROP {}", fieldInfo.getTableInfo().getDbInfo().getOwner(), fieldInfo.getTableInfo().getTableName(), fieldInfo.getFieldName());
    }


    @Override
    public String funD2C(String fieldName, boolean dateOrTimestamp)
    {
        return dateOrTimestamp ? String.format("CHAR(%s)", fieldName) : String.format("TIMESTAMP(%s)", fieldName);
    }

}
